#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
创建完全按照用户要求格式的Excel文件
包含合并的表头和正确的列结构
"""

import json
import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Alignment, Font, Border, Side
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.worksheet.worksheet import Worksheet

def load_ems_data(filename: str = "japanpost_ems_prices.json") -> list:
    """加载EMS运费数据"""
    try:
        with open(filename, 'r', encoding='utf-8') as f:
            data = json.load(f)
        print(f"✅ 成功加载 {len(data)} 条运费数据")
        return data
    except Exception as e:
        print(f"❌ 加载数据失败：{e}")
        return []

def create_exact_excel_format(data: list, filename: str = "japanpost_ems_exact_format.xlsx"):
    """创建完全按照要求格式的Excel文件"""
    print("📊 创建完全按照要求格式的Excel文件...")
    
    # 创建新的工作簿
    wb = Workbook()
    ws = wb.active
    ws.title = "EMS运费表"
    
    # 设置列宽
    ws.column_dimensions['A'].width = 15  # 重量列
    for col in ['B', 'C', 'D', 'E', 'F', 'G']:
        ws.column_dimensions[col].width = 12
    
    # 创建表头
    # 第一行：重量 + 区（合并5列）
    ws['A1'] = '重量'
    ws.merge_cells('B1:F1')
    ws['B1'] = '区'
    
    # 第二行：地区 + 5个区域的具体名称
    ws['A2'] = '地区'
    ws['B2'] = '第1区'
    ws['C2'] = '第2区'
    ws['D2'] = '第3区'
    ws['E2'] = '第4区'
    ws['F2'] = '第5区'
    
    # 第三行：各区域包含的国家/地区
    ws['A3'] = ''  # 重量列留空
    ws['B3'] = '中国、韩国、台湾'
    ws['C3'] = '亚洲（中国、韩国、台湾除外）'
    ws['D3'] = '大洋洲、加拿大、墨西哥、中近东、欧洲'
    ws['E3'] = '美国（包括关岛等海外领土）'
    ws['F3'] = '中南美（墨西哥除外）、非洲'
    
    # 设置表头样式
    header_font = Font(bold=True, size=12)
    header_alignment = Alignment(horizontal='center', vertical='center')
    
    # 第一行样式
    ws['A1'].font = header_font
    ws['A1'].alignment = header_alignment
    ws['B1'].font = header_font
    ws['B1'].alignment = header_alignment
    
    # 第二行样式
    for col in ['A', 'B', 'C', 'D', 'E', 'F']:
        cell = ws[f'{col}2']
        cell.font = Font(bold=True, size=11)
        cell.alignment = Alignment(horizontal='center', vertical='center')
    
    # 第三行样式
    for col in ['B', 'C', 'D', 'E', 'F']:
        cell = ws[f'{col}3']
        cell.font = Font(size=10)
        cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
    
    # 添加数据行
    row_num = 4
    for item in data:
        # 重量列
        ws[f'A{row_num}'] = item['weight_text']
        ws[f'A{row_num}'].alignment = Alignment(horizontal='left', vertical='center')
        
        # 各区域价格
        zones = ['第1区', '第2区', '第3区', '第4区', '第5区']
        for i, zone in enumerate(zones):
            col = chr(ord('B') + i)
            if zone in item['prices']:
                price = item['prices'][zone]
                ws[f'{col}{row_num}'] = price
                ws[f'{col}{row_num}'].alignment = Alignment(horizontal='right', vertical='center')
                ws[f'{col}{row_num}'].number_format = '#,##0'
        
        row_num += 1
    
    # 设置边框
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )
    
    # 为整个表格添加边框
    for row in range(1, row_num):
        for col in range(1, 7):  # A到F列
            cell = ws.cell(row=row, column=col)
            cell.border = thin_border
    
    # 保存文件
    try:
        wb.save(filename)
        print(f"✅ Excel文件已保存: {filename}")
        return True
    except Exception as e:
        print(f"❌ 保存Excel文件失败: {e}")
        return False

def create_alternative_format(data: list, filename: str = "japanpost_ems_alternative.xlsx"):
    """创建替代格式的Excel文件（使用pandas，可能格式更标准）"""
    print("📊 创建替代格式的Excel文件...")
    
    # 准备数据
    excel_data = []
    
    for item in data:
        row = {
            '重量': item['weight_text'],
            '第1区': item['prices'].get('第1区', ''),
            '第2区': item['prices'].get('第2区', ''),
            '第3区': item['prices'].get('第3区', ''),
            '第4区': item['prices'].get('第4区', ''),
            '第5区': item['prices'].get('第5区', '')
        }
        excel_data.append(row)
    
    # 创建DataFrame
    df = pd.DataFrame(excel_data)
    
    # 保存为Excel
    try:
        with pd.ExcelWriter(filename, engine='openpyxl') as writer:
            df.to_excel(writer, sheet_name='EMS运费表', index=False)
            
            # 获取工作表
            worksheet = writer.sheets['EMS运费表']
            
            # 插入表头行
            worksheet.insert_rows(1, 2)
            
            # 设置第一行：重量 + 区（合并5列）
            worksheet['A1'] = '重量'
            worksheet.merge_cells('B1:F1')
            worksheet['B1'] = '区'
            
            # 设置第二行：地区 + 5个区域
            worksheet['A2'] = '地区'
            worksheet['B2'] = '第1区'
            worksheet['C2'] = '第2区'
            worksheet['D2'] = '第3区'
            worksheet['E2'] = '第4区'
            worksheet['F2'] = '第5区'
            
            # 设置第三行：各区域包含的国家/地区
            worksheet['A3'] = ''
            worksheet['B3'] = '中国、韩国、台湾'
            worksheet['C3'] = '亚洲（中国、韩国、台湾除外）'
            worksheet['D3'] = '大洋洲、加拿大、墨西哥、中近东、欧洲'
            worksheet['E3'] = '美国（包括关岛等海外领土）'
            worksheet['F3'] = '中南美（墨西哥除外）、非洲'
            
            # 设置列宽
            worksheet.column_dimensions['A'].width = 20
            for col in ['B', 'C', 'D', 'E', 'F']:
                worksheet.column_dimensions[col].width = 15
        
        print(f"✅ 替代格式Excel文件已保存: {filename}")
        return True
        
    except Exception as e:
        print(f"❌ 保存替代格式Excel文件失败: {e}")
        return False

def main():
    """主函数"""
    print("🚀 开始创建完全按照要求格式的Excel文件...")
    print("=" * 60)
    
    # 加载数据
    data = load_ems_data()
    if not data:
        print("❌ 无法加载数据")
        return
    
    # 创建两种格式的Excel文件
    success1 = create_exact_excel_format(data, "japanpost_ems_exact_format.xlsx")
    success2 = create_alternative_format(data, "japanpost_ems_alternative.xlsx")
    
    if success1 and success2:
        print("\n🎉 所有Excel文件创建完成！")
        print("\n📁 生成的文件:")
        print("  1. japanpost_ems_exact_format.xlsx - 完全按照要求格式")
        print("  2. japanpost_ems_alternative.xlsx - 替代格式")
        print("\n💡 建议:")
        print("  - 第一个文件格式更接近您的要求")
        print("  - 第二个文件格式更标准，便于数据处理")
    else:
        print("\n⚠️  部分文件创建失败，请检查错误信息")

if __name__ == "__main__":
    main()
